﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsPlanDataAccess
    {
        public static DataTable SelectList(string PlanID, string From, string End, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select * from V_PlanDetail p where 1=1";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(PlanID))
                {
                    strcmd += " and p.PlanID like @PlanID";
                    dpc.AddWithValue("@PlanID", "%" + PlanID + "%");
                }
                if (!string.IsNullOrEmpty(From))
                {
                    strcmd += " and p.BillDate >= @From";
                    dpc.AddWithValue("@From", From + " 00:00:00");
                }
                if (!string.IsNullOrEmpty(End))
                {
                    strcmd += " and p.BillDate <= @End";
                    dpc.AddWithValue("@End", End + "23:59:59");
                }
                strcmd += " order by p.PlanID desc";
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable SelectDialogList(string ProductID, string ProductName, string userid, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select b.*,od.OrderDetailID,od.OrderID,
                                    od.CustomerPartNo,od.Nums,od.Price,od.RMBPrice,
                                    od.Amount,od.TaxAmount,od.IsFinish,p.ProductTypeID,p.ProductName,
                                    p.Specifications,p.OutSide,p.InSide,p.Unit,p.SafeStock,pt.ProductTypeName,
                                    ISNULL(od.Nums,0)-ISNULL(c.PlanNums,0) as LastNum
                                    from BS_BOM b
                                    left join BS_OrderDetail od on od.WorkOrderNo=b.WorkOrderNo
                                    left join MD_Product p on p.ProductID=b.ProductID
                                    left join MD_ProductType pt on pt.ProductTypeID=p.ProductTypeID
                                    left join (select SUM(ISNULL(PlanNum,0)) PlanNums,WorkOrderNo from BS_Plan group by WorkOrderNo) c on c.WorkOrderNo=b.WorkOrderNo
                                    where ISNULL(od.Nums,0)-ISNULL(c.PlanNums,0)>0";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(ProductID))
                {
                    strcmd += " and b.ProductID like @ProductID";
                    dpc.AddWithValue("@ProductID", "%" + ProductID + "%");
                }
                if (!string.IsNullOrEmpty(ProductName))
                {
                    strcmd += " and p.ProductName like @ProductName";
                    dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                }
                strcmd += " order by b.BOMID desc";
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable SelectPlanByWorkOrderNo(string workorderno)
        {
            try
            {
                string strcmd = @"select * from V_BOM p where p.WorkOrderNo=@workorderno";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@workorderno", workorderno);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetDataByPlanID(string id)
        {
            try
            {
                string strcmd = @"select v.*,c.PlanNums,ISNULL(v.Nums,0)-ISNULL(c.PlanNums,0) as LastNum from V_PlanDetail v
                                    left join (select SUM(ISNULL(PlanNum,0)) as PlanNums,WorkOrderNo from BS_Plan where PlanID<>@PlanID group by WorkOrderNo) c on c.WorkOrderNo=v.WorkOrderNo
                                    where PlanID=@PlanID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@PlanID", id);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
